create or replace type FacultyType2 AS object(
facid	varchar2(6),
name	varchar2(20),
dept	varchar2(20),
rank	varchar2(15));

create table Faculty2 of FacultyType2 (
constraint Fac2_facid_pk primary key (facid),
constraint Fac2_rank_cc check (rank in ('Instructor', 'Assistant','Associate','Professor')));

create or replace type studenttype2 as object(
stuid		varchar2(6),
lastName	varchar2(20),
firstName	varchar2(20),
address		Addresstype,
advid		REF FacultyType2,
credits		number(3),
dateOfBirth	date)
INSTANTIABLE
NOT FINAL;
/


create table Student2 of StudentType2(
constraint Stu2_stuid_pk primary key(stuid));

alter table Student2 add (scope for (advid) is faculty2);
alter table Student2 add constraint Stu2_advid_fk foreign key (advid) references Faculty2 on delete set null;

insert into faculty2 values(facultytype2('F101','Smith','History','Associate'));

insert into Student2 values(studenttype2('S555', 'Hughes','John',addresstype('101 Oak Lane','Austin','TX','73301'),(select ref(f) from faculty2 f where facid='F101'), 0,'01-Jan-1988'));	

select value(f) from faculty2 f where facid='F101';

select deref(s.advid) from student2 s where stuid='S555';


select stuid, lastname, firstname, s.advid.name, s.advid.dept from student2 s where stuid='S555';

--creating the StudentType2 Hierarchy
create or replace type UndergraduateType2 UNDER StudentType2(
	major	varchar2(15))
instantiable
not final;


create or replace type freshmantype2 UNDER UndergraduateType2(
peerMentor	varchar2(25))
instantiable
final;

-- entering all subtypes of Student2 in the same table
INSERT INTO Student2 VALUES
(StudentType2('S444','Klein','Susan', AddressType('123 Hudson Street','New York','NY','10001'),(SELECT REF(f) FROM Faculty2 f WHERE facId = 'F101'),36, '14-Mar-1976'));

INSERT INTO Student2 VALUES (UndergraduateType2('S666','Logan','Randolph', null, null, 12,'23-May-1982','Spanish'));

INSERT INTO Student2 VALUES
(FreshmanType2('S777','Miller','Terrence',AddressType('999 Salmon Street','Sitka','AK','99835'),(SELECT REF(f)FROM Faculty2 f WHERE facId = 'F101'),30,'30-Jun-1985', 'Math','Tom Smith'));

-- retrieving subtypes

SELECT VALUE(s) 
FROM Student2 s;

-- find students who are not undergraduate or freshman types
SELECT VALUE(s)
FROM Student2 s
WHERE value(s) IS OF (ONLY StudentType2) ;

-- find only undergraduates, including freshman type --
SELECT VALUE(s)
FROM Student2 s
WHERE VALUE(s) IS OF (UndergraduateType2);

-- find only freshman type
SELECT VALUE(s)
FROM Student2 s
WHERE VALUE(s) IS OF (ONLY FreshmanType2);

-- the following produces an error message because StudentType2 does not have peermentor--
SELECT lastName, firstName, s.peerMentor
FROM Student2 s
WHERE VALUE(s) IS OF (FreshmanType2);

-- to correct the error, must use TREAT to access peerMentor attribute--
SELECT lastName, firstName, TREAT(VALUE(s) AS FreshmanType2).peerMentor
FROM Student2 s
WHERE VALUE(s) IS OF (FreshmanType2);


